Add a custom icon to a custom command bar

You can add an icon directly to your custom command bar directly through Excel with no need for Add-Ins.

First you’ll need to create your icon. You can do this using Paint. Make sure you save your icon as a 16 x 16 pixel 16-colour bmp – when designing it, zoom at 800% for greater clarity. Once created, save to the same path as your workbook.

You’ll also need to create a mask for the custom icon – do this by selecting Black & White from the Attributes in the Image options. The mask should be called something like IconMask.bmp.

Now you’re ready to run your code. This example is based on the previous custom command bar – the difference is that an additional control button is added to show the new icon. The code gets the icon and the icon mask from the same file path as your workbook – if your icons aren’t stored here, just change the code accordingly.

__________________________

Public Sub AddCustomIcon()

Dim cbar As Object
Dim Cmb As Object
Dim SubMenu As Object
Dim NewCb As Object
Dim SubMenu1 As Object
Dim SubMenu2 As Object
Dim SubMenu3 As Object
Dim SubMenu4 As Object
Dim strPath As String
On Error Resume NextSet cbar = CommandBars.Add(Name:=”Sculpt”, Position:=msoBarTop,

Temporary:=True)
cbar.Visible = True
cbar.Enabled = True

Dim NewMenu As Object
Set NewMenu = CommandBars(“Sculpt”)
With NewMenu
.Visible = True
.Enabled = True
.Controls.Add(Type:=msoControlButton, before:=1).Caption = “SculptIcon”
.Controls.Add(Type:=msoControlPopup, before:=2).Caption = “Sculpt”
.Controls.Add(Type:=msoControlDropdown, before:=3).Caption = _ “Select period”
.Controls.Add(Type:=msoControlButton, before:=4).Caption = “Get data”
End With

‘Add the custom icon
strPath = ThisWorkbook.Path ‘filepath of your workbook
If Right(strPath, 1) <> “\” Then strPath = strPath & “\”
Set SubMenu = CommandBars(“Sculpt”).Controls(“SculptIcon”)
With SubMenu
.Picture = LoadPicture(strPath & “Sculpt.bmp”)
.Mask = LoadPicture(strPath & “SculptMask.bmp”)
End With

‘Add the custom submenus
Set SubMenu1 = CommandBars(“Sculpt”).Controls(“Sculpt”)
With SubMenu1
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = “Print”
.Controls.Add(Type:=msoControlButton, before:=2).Caption = “Get Budgets”
End With

‘Add the dropdown list items
Set Cmb = CommandBars(“Sculpt”).Controls(“Select period”)
With Cmb
.Width = 120
.Caption = “Select Month”
.Visible = True
.Enabled = True
.AddItem “November 2006”, 1
.AddItem “December 2006”, 2
.AddItem “January 2007”, 3
.AddItem “February 2007”, 4
.AddItem “March 2007”, 5
.ListIndex = 1
‘.OnAction = “‘” & ActiveWorkbook.Name & “‘!ShowValue”
End With

Set NewCb = CommandBars(“Sculpt”).Controls(“Get Data”)
With NewCb
.Caption = “Get data”
.Visible = True
.FaceId = 7433
End With

‘Now add sub-menu items
Set SubMenu2 = CommandBars(“Sculpt”).Controls(“Sculpt”).Controls(“Print”)
With SubMenu2
.Controls.Add(Type:=msoControlButton, before:=1).Caption = _ “All Records”
.Controls(“All Records”).FaceId = 109
.Controls.Add(Type:=msoControlButton, before:=2).Caption = _ “Active Records”
.Controls(“Active Records”).FaceId = 928
End With

Set SubMenu3 = CommandBars(“Sculpt”).Controls(“Get data”)
With SubMenu3
.OnAction = “‘” & ActiveWorkbook.Name & “‘!SubName”
End With

Set SubMenu4 = CommandBars(“Sculpt”).Controls(“Sculpt”).Controls(“Get Budgets”)
With SubMenu4
.OnAction = “‘” & ActiveWorkbook.Name & “‘!SubName”
End With

‘Begin groups
Application.CommandBars(“Sculpt”).Controls(“Sculpt”) _
.Controls(“Get budgets”).BeginGroup = True

Set NewMenu = CommandBars(“Sculpt”)
With NewMenu
.Protection = msoBarNoChangeVisible
End With

Sculpt